<?php 
$lang=$_REQUEST['lang'];
$company=$_REQUEST['company'];
$database = 'elitech';
$user_name=$_REQUEST['user_name'];

$lang="KR";
$company="elitech";
$database = 'elitech';
$user_name="Mai Van Duong";
//echo $database;

	try {
		ini_set('display_errors', 0);
		$connection = mysql_connect("1.234.53.52", "cam", "5678" ) ;
		if(!$connection){
			echo "khong connect duoc<br>";		
		}		
		if(!mysql_select_db($database,$connection)){	
			echo "khong select duoc db<br>";	
		}
		if(!mysql_query("SET CHARACTER SET 'utf8'", $connection)){	
			echo "khong set character<br>";	
		}
	} catch (Exception $e) {
		echo 'Loi';	
	}

	
	if (empty($lang) || $lang == "EN") {
		$value = "value/value_en.php";
	} else if($lang == "VN") {
		$value = "value/value_vn.php";
	}
	else {
		$value = "value/value_kr.php";
	}
	include($value);
include ("sa_funcs.php");

$yesterday=date("Y-m-d", time() - 86400);
$today=date("Y-m-d", time());

if(getWday($yesterday)!=6){
$year=date("Y", time() - 86400);
$month=date("m", time() - 86400);
}else{
	//$yesterday=date("Y-m-d", time() - 86400 - 86400);
	$year=date("Y", time() - 86400 - 86400);
	$month=date("m", time() - 86400 - 86400);
}


$mthu=array();
$mthu[0]='<font color="red" ><b>'.$l_vday_chunhat.'</b></font>';
$mthu[1]=$l_vday_thuhai;
$mthu[2]=$l_vday_thuba;
$mthu[3]=$l_vday_thutu;
$mthu[4]=$l_vday_thunam;
$mthu[5]=$l_vday_thusau;
$mthu[6]='<font color="red" ><b>'.$l_vday_thubay.'</b></font>';
$mthu[7]=" ";	
	
	$query=mysql_query("SELECT * FROM sa_check_time sa WHERE sa.send_mail='0' AND sa.time_work<'".$today."';");
	
		
		// file 03-gmail.php
		
		//NhÃºng thÆ° viá»‡n phpmailer
		/*require_once('class.phpmailer.php');
		
		//Khá»Ÿi táº¡o Ä‘á»‘i tÆ°á»£ng
		$mail = new PHPMailer();
		
		$mail->IsSMTP(); // Gá»�i Ä‘áº¿n class xá»­ lÃ½ SMTP
		//$mail->Host       = "mail.zend.vn"; // tÃªn SMTP server
		$mail->SMTPDebug  = 2;                    // enables SMTP debug information (for testing)
		                                           // 1 = errors and messages
		                                           // 2 = messages only
		$mail->SMTPAuth   = true;                  // Sá»­ dá»¥ng Ä‘Äƒng nháº­p vÃ o account
		$mail->SMTPSecure = "ssl";
		$mail->Host       = "smtp.gmail.com";     // Thiáº¿t láº­p thÃ´ng tin cá»§a SMPT
		$mail->Port       = 465;                     // Thiáº¿t láº­p cá»•ng gá»­i email cá»§a mÃ¡y
		$mail->Username   = "elisoftmailserver@gmail.com"; // SMTP account username
		$mail->Password   = "1234567892727";         // SMTP account password
		
		//Thiet lap thong tin nguoi gui va email nguoi gui
		//$yesterday=date("Y-m-d", time() - 86400);
		$mail->SetFrom('elisoftmailserver@gmail.com',$company.' - '.$l_working_spreadsheet);
		
		//Thiáº¿t láº­p thÃ´ng tin ngÆ°á»�i nháº­n
		//$mail->AddAddress("vinhelisoftvietnam@gmail.com", "Lê Quang Vinh");
		//$mail->AddAddress("kisadlu@gmail.com", "meokisa");
		//$mail->AddAddress("klkim169@gmail.com", "klkim");
		//$mail->AddAddress("ptitduong@gmail.com", "ptitduong");
		$mail->AddAddress("orchipro@gmail.com", "orchipro");
		
		$mail->Subject    =  $company.' - '.$l_working_spreadsheet.' : '.$yesterday;
		
		$mail->CharSet = "utf-8";
		$mail->IsHTML(true);*/
	
		
		$body="";
		$body ='<table width="100%"><tr><td style="padding:10px;color:black; font-weight:bold; font-size:13px;text-align:center;">'.$l_working_spreadsheet.' : '.$yesterday.'</td></tr></table>';
		$body .='<table style="width:100%;border:1px #000000 solid;">';
		$body .='<tr style="background:#EEEEEE;font-weight:bold;text-align:center;border-bottom:1px #000000 solid;">';
			$body .='<td width="10px" style="border-left: 1px #000000 dotted;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_no.'</td>';
			$body .='<td width="20%" style="border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_employee_name.'</td>';
			$body .='<td width="4%" style="border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_code.'</td>';
			$body .='<td width="5%" style="min-width: 75px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_thu.'</td>';
			$body .='<td width="6%" style="min-width: 88px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_date.'</td>';
			$body .='<td width="5%" style="border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_timecheckin.'</td>';
			$body .='<td width="5%" style="border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_timecheckout.'</td>';
			$body .='<td width="5%" style="border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">Today</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_late.'</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_comebackearly.'</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_half_dayoff.'</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_all_dayoff.'</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_overtime.'</td>';
			$body .='<td width="10%" style="border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_note.'</td>';
			
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_late.' (s)</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_comebackearly.' (s)</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_half_dayoff.'(s)</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_all_dayoff.' (s)</td>';
			$body .='<td  style="min-width: 35px;border-right: 1px #000000 dotted;border-bottom: 1px #000000 dotted">'.$l_overtime.' (s)</td>';
			
		$body .='</tr>';
		$i=0;
		while($row=mysql_fetch_array($query)){
			$temp1 = explode('-',$row['time_work']);
			$month=$temp1[1];
			$year=$temp1[0];
			$thu=getWday($row['time_work']);
			if($thu==6 || $thu==0){
				$body .='<tr style="text-align:center;background:#ccffcc;">';
			}else{
				$body .='<tr style="text-align:center">';
			}
				$body .='<td style="border: 1px #000000 dotted">'.($i+1).'</td>';
				$body .='<td style="border: 1px #000000 dotted;text-align:left;">'.$row['employeeName'].'</td>';
				$body .='<td style="border: 1px #000000 dotted">'.$row['employeeID'].'</td>';
				$body .='<td style="border: 1px #000000 dotted">'.$mthu[$thu].'</td>';
				$body .='<td style="border: 1px #000000 dotted">'.$row['time_work'].'</td>';
				
				if($row['is_late']==1)
					$body .='<td style="background:orange;border: 1px #000000 dotted">'.$row['time_in'].'</td>';
				else 
					$body .='<td style="border: 1px #000000 dotted">'.$row['time_in'].'</td>';
				
				$body .='<td style="border: 1px #000000 dotted">'.$row['time_out'].'</td>';
				
				$query2=mysql_query("SELECT sa.time_in FROM sa_check_time sa WHERE sa.send_mail='0' AND sa.time_work='".$today."' AND sa.employeeID='".$row['employeeID']."';");
				$row2=mysql_fetch_assoc($query2);
				
				$temp = explode(':',$row2['time_in']);
				$inTime = $temp[0].'.'.$temp[1];
				$l=0;
				$e=0;
				if($inTime<=8)
					$body .='<td style="border: 1px #000000 dotted">'.$row2['time_in'].'</td>';
				else if($inTime<=8.30){
					$l=$l+1;
					$body .='<td style="background:orange;border: 1px #000000 dotted">'.$row2['time_in'].'</td>';
				}
				else if($inTime<=10){
					$e=$e+1;
					$body .='<td style="background:yellow;border: 1px #000000 dotted">'.$row2['time_in'].'</td>';
				}
					
					
				if($row['is_late']==1){
					$body .='<td  style="background:orange;font-weight:bold;border: 1px #000000 dotted;text-align:center;">X</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted"></td>';
				}
				if($row['is_comeback_early']==1){
					$body .='<td  style="background:yellow;font-weight:bold;border: 1px #000000 dotted;text-align:center;">X</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted;text-align:center;"></td>';
				}
				//$body .='<td class="rf-dt-c" >'.$row[is_comeback_early].'</td>';
				if($row['half_day_off']==1){
					$body .='<td  style="background:pink;font-weight:bold;border: 1px #000000 dotted;text-align:center;">X</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted"></td>';
				}
				//$body .='<td class="rf-dt-c" >'.$row[half_day_off].'</td>';
				if($row['all_day_off']==1){
					$body .='<td  style="background:red;font-weight:bold;border: 1px #000000 dotted;text-align:center;">X</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted"></td>';
				}
				//$body .='<td class="rf-dt-c" >'.$row[all_day_off].'</td>';
				if($row['overtime']==1){
					$body .='<td  style="background:green;font-weight:bold;border: 1px #000000 dotted;text-align:center;">X</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted"></td>';
				}
				
				$body .='<td style="border: 1px #000000 dotted;text-align:center;">'.$row['note'].'</td>';
				
				$query1=mysql_query("SELECT SUM(sa.is_late) as lates,SUM(sa.is_comeback_early) as comeback_earlys,SUM(sa.half_day_off) as half_day_offs,SUM(sa.all_day_off) as all_day_offs,SUM(sa.overtime) as overtimes FROM sa_check_time sa WHERE sa.time_work >='".$year."-".$month."-01' AND sa.time_work <'".$today."' AND sa.employeeID='".$row['employeeID']."' GROUP BY sa.employeeID");
				$row1=mysql_fetch_assoc($query1);
				if(($row1['lates']+$l)>0){
					$body .='<td  style="background:orange;font-weight:bold;border: 1px #000000 dotted;text-align:center;">'.($row1['lates']+$l).'</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted">'.($row1['lates']+$l).'</td>';
				}
				if(($row1['comeback_earlys']+$e)>0){
					$body .='<td  style="background:yellow;font-weight:bold;border: 1px #000000 dotted;text-align:center;">'.($row1['comeback_earlys']+$e).'</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted;text-align:center;">'.($row1['comeback_earlys']+$e).'</td>';
				}
				//$body .='<td class="rf-dt-c" >'.$row[is_comeback_early].'</td>';
				if($row1['half_day_offs']>0){
					$body .='<td  style="background:pink;font-weight:bold;border: 1px #000000 dotted;text-align:center;">'.$row1['half_day_offs'].'</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted">'.$row1['half_day_offs'].'</td>';
				}
				//$body .='<td class="rf-dt-c" >'.$row[half_day_off].'</td>';
				if($row1['all_day_offs']>0){
					$body .='<td  style="background:red;font-weight:bold;border: 1px #000000 dotted;text-align:center;">'.$row1['all_day_offs'].'</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted">'.$row1['all_day_offs'].'</td>';
				}
				//$body .='<td class="rf-dt-c" >'.$row[all_day_off].'</td>';
				if($row1['overtimes']>0){
					$body .='<td  style="background:green;font-weight:bold;border: 1px #000000 dotted;text-align:center;">'.$row1['overtimes'].'</td>';
				}else{
					$body .='<td  style="border: 1px #000000 dotted">'.$row1['overtimes'].'</td>';
				}
				
				
				
				
				
				
			$body .='</tr>';
			$i=$i+1;
			
		}
		
	$body .='</table>';
	
	
	
	
	$body .='<br><table width="100%" style="padding:20px" >
	<tr><td align="right"><b>'.$l_sender.':</b> <b><i>'.$user_name.'</i></b></td></tr>
	</table>';
	//mysql_query("UPDATE `sa_check_time` SET `send_mail`='1' WHERE (`time_work`<'$today');");
	echo $body;
	/*$mail->Body = $body;
		
	if(!$mail->Send()) {
	  	echo "Mailer Error: " . $mail->ErrorInfo;
	} else {
	 	echo "Sent Successfully";
	}*/
		
		//reference
		
mysql_close($connection);
?>